package com.hsnn.medstgmini.pro.plan.dao.impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.model.DicArea;
import com.hsnn.medstgmini.base.dict.model.DicDrugform;
import com.hsnn.medstgmini.base.dict.model.DicHosptype;
import com.hsnn.medstgmini.base.std.dao.StdHospInfoDao;
import com.hsnn.medstgmini.base.std.model.HospInfo;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.model.StdDrugInfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.plan.dao.BidHospplanDao;
import com.hsnn.medstgmini.pro.plan.model.BidHospplan;
import com.hsnn.medstgmini.pro.project.dao.BidProjectDao;
import com.hsnn.medstgmini.pro.project.model.BidProject;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
@Repository("bidHospplanDao")
public class BidHospplanDaoImpl implements BidHospplanDao {
	private static final Logger log = Logger.getLogger(BidHospplanDaoImpl.class);
	@Autowired
	private BidProjectDao bidProjectDao;
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	private Session getSession(){
		Session session = sessionFactory.getCurrentSession();
		if (null == session){
			session = sessionFactory.openSession();
		}
		return session;
	}
	@Autowired
	private StdHospInfoDao stdHospInfoDao;
	
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * 
	 *@category 获取当前医疗机构可申报采购计划列表
	 *@author 邱磊
	 *@date 2015年6月12日 下午2:04:37
	 *@param page
	 *@return
	 */
	public Pagination getDataList(Pagination page){
		String hospCode = page.getConditions().get("hospCode");
		String projCode = page.getConditions().get("projCode");
		String name = page.getConditions().get("name");
		//点击申报采购计划列表（带有已申报的药品）
		StringBuffer sql = new StringBuffer()
		.append("	SELECT   a.hosp_code hospId,c.proj_id projId,c.status status,")
		.append("	c.proj_code projCode, c.name name ,c.proj_intro projIntro,")
		.append("	c.plan_start_datetime starttime,     ")   
		.append("	c.plan_end_datetime endtime,c.proj_cat projCat,     ")
		.append("	(SELECT COUNT(*) FROM bid_hospplan b  WHERE      ")
		.append("	b.hosp_code=a.hosp_code AND b.proj_id=c.proj_id) drugCount")      
		.append("	FROM  bid_projhospital a ")
		.append("	LEFT JOIN bid_project c   ON  c.proj_id = a.proj_id")       
		.append("	WHERE     a.status = 1   ")
		.append("	AND c.status = 1  ")
		.append("	AND c.plan_start_datetime < :currentTime ") 
		.append("	AND c.plan_end_datetime > :currentTime ")
		.append("	AND a.hosp_code = :hospCode    ");
		
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("hospCode", hospCode);
		params.put("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		if(StringUtils.isNotBlank(projCode)){
			sql.append(" and c.proj_code = :projCode ");
			params.put("projCode", projCode);
		}
		if(StringUtils.isNotBlank(name)){
			sql.append(" and c.name like :name ");
			params.put("name", "%"+name+"%");
		}
		sql.append(" order by c.proj_code desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * 
	 *@category 获取当前医疗机构未申报药品列表
	 *@author 邱磊
	 *@date 2015年6月12日 下午4:13:54
	 *@param page
	 *@return
	 */
	public Pagination getDrugDataList(Pagination page){
		String projId = page.getConditions().get("projId");
		String hospCode = page.getConditions().get("hospCode");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		//未申报的药品
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.proj_drug_id projDrugId,b.drug_code drugCode,c.qualityspec_name drugName,")
		.append("	c.form_name formName,c.spec_name specName,b.factor factor,b.unit_name unit,      ")
		.append("	b.material_name materialName,b.comp_name prodPompName,b.comp_code compCode,b.drug_code drugId,a.proj_id projId")        
		.append("	FROM  bid_projdrugs a      ")
		.append("	LEFT JOIN  std_proddruginfo b    ON a.drug_code=b.drug_code")    
		.append("	LEFT JOIN std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id")       
		.append("	WHERE  a.proj_id = :projId  AND NOT EXISTS")
		.append("	(SELECT * FROM  bid_hospplan d WHERE d.proj_id=a.proj_id")  
		.append("	AND d.drug_code=a.drug_code   AND d.hosp_code = :hospCode) ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("hospCode", hospCode);
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (c.qualityspec_name like :drugName or c.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_name_spel like :compName) ");
			params.put("compName", "%"+compName+"%");
		}
		sql.append(" order by b.drug_code ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	/**
	 * 
	 *@category 获取当前医疗机构已申报药品列表
	 *@author 应晓川
	 *@date 2015年6月28日 
	 *@param page
	 *@return
	 */
	public Pagination gotDrugDataList(Pagination page){
		String projId = page.getConditions().get("projId");
		String hospCode = page.getConditions().get("hospCode");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String prodPompId = page.getConditions().get("prodPompId");
		//已申报的药品		
		StringBuffer sql = new StringBuffer()
		.append("	SELECT d.proj_plan_id projPlanId,d.proj_id projId,d.hosp_code hospId,b.drug_code drugId,")
		.append("	b.drug_code drugCode,c.qualityspec_name drugName,c.form_name formName,c.spec_name specName,    ")    
		.append("	b.factor factor,b.unit_name unit,b.material_name materialName,b.comp_name prodPompName,b.comp_code compCode,       ")
		.append("	d.lasty_pro_amt lastyProAmt,d.plan_pro_amt planProAmt,d.pro_method proMethod,       ")
		.append("	d.quality_requires qualityRequires   FROM     bid_hospplan d ")
		.append("	LEFT JOIN     std_proddruginfo b    ON d.drug_code=b.drug_code  ")
		.append("	LEFT JOIN std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id")       
		.append("	WHERE  d.proj_id = :projId AND d.hosp_code = :hospCode ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("hospCode", hospCode);
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (c.qualityspec_name like :drugName or c.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(prodPompId)){
			sql.append(" and (b.comp_name like :compName or b.comp_name_spel like :compName) ");
			params.put("compName", "%"+prodPompId+"%");
		}
		sql.append(" order by b.drug_code ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	public int addDrugData(Map<String,String> data){
		String projPlanId  = UUID.randomUUID().toString();
		String projId = data.get("projId");
		String drugCode = data.get("drugCode");
		String hospCode = data.get("hospCode");
		String areaId = data.get("areaId");
		String lastyProAmt = data.get("lastyProAmt");
		String planProAmt = data.get("planProAmt");
		String proMethod = data.get("proMethod");
		String qualityRequires = data.get("qualityRequires");
		String updDatetime = data.get("updDatetime"); 
		String userId = data.get("userId");
		
		Session session = getSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into bid_hospplan values (?,?,?,?,?,?,?,?,?,?,?,?)");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter(0, projPlanId);
		query.setParameter(1, projId);
		query.setParameter(2, drugCode);
		query.setParameter(3, null);
		query.setParameter(4, hospCode);
		query.setParameter(5, areaId);
		query.setParameter(6, lastyProAmt);
		query.setParameter(7, planProAmt);
		query.setParameter(8, proMethod);
		query.setParameter(9, qualityRequires);
		query.setParameter(10, updDatetime);
		query.setParameter(11, userId);
		int count = query.executeUpdate();
		getSession().flush();	
		return count;
	}
	public int batchAddDrugData(List<Map<String,String>> list){
		int sum = 0;
		if (null != list && list.size() > 0){
			for (int i = 0; i < list.size(); i++){
				int count = addDrugData(list.get(i));
				sum += count;
			}
			
		}
		return sum;
	}
	
	/**
	 * @category 采购计划列表(按药品汇总)
	 * @author 单大伟
	 * @date 2015年6月13日
	 * @param page
	 * @param bidHospplan
	 * @return 采购计划列按药品汇总列表
	 */
	@SuppressWarnings("unchecked")
	public List<Object> getBidHospplanList(Pagination page, BidHospplan bidHospplan, BidProject bidProject, StdDrugInfo stdDrugInfo, HospInfo hospInfo, DicDrugform dicDrugform, StdCompinfo stdCompinfo) {
		Session session = sessionFactory.getCurrentSession();
		// 查询总记录数
		StringBuffer sqlHead = new StringBuffer();
		sqlHead.append(" select bhp.proj_id, vd.drug_code drug_id, vd.drug_code, b.qualityspec_name drug_name, b.form_name, ")
		   .append(" b.spec_name, vd.factor, vd.unit_name unit, vd.material_name, vd.comp_name,vd.comp_code, count(bhp.hosp_code) as hosp_count ");
		
		
		
		
		StringBuffer sqlEnd = new StringBuffer();
		sqlEnd.append(" from bid_hospplan bhp left join std_proddruginfo vd on  vd.drug_code=bhp.drug_code ")
		      .append("  left join std_drugqualityspec b on vd.qualityspec_id = b.qualityspec_id ")
		      .append(" where bhp.drug_code = vd.drug_code ")
		      .append(" and bhp.proj_id = :projId ");
		
		if (stdDrugInfo != null) {
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugCode())) {
				sqlEnd.append(" and vd.drug_code = :drugCode ");
			}
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugName())) {
				sqlEnd.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName)");
			}
		}
		
		if (stdCompinfo != null) {
			if (StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				sqlEnd.append(" and (vd.comp_name like :compName or vd.comp_name_spel like :compName)");
			}
		}
		
		sqlEnd.append(" group by bhp.drug_code  having count(bhp.hosp_code) > 0 order by hosp_count desc ");
		
		StringBuffer countSql = new StringBuffer();
		//这里的as hosp_count 是为了后面的 order by hosp_count desc中的 hosp_count已定义
		String sql = sqlHead.append(sqlEnd).toString();
		countSql.append(" select count(*) from ( ").append(sql).append(")as b where 1=1");
		Query query = session.createSQLQuery(sql);
		Query countQuery = session.createSQLQuery(countSql.toString());
		query.setString("projId", bidProject.getProjId());
		countQuery.setString("projId", bidProject.getProjId());
		
		if (stdDrugInfo != null) {
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugCode())) {
				query.setString("drugCode", stdDrugInfo.getDrugCode().trim());
				countQuery.setString("drugCode", stdDrugInfo.getDrugCode().trim());
			}
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugName())) {
				query.setString("drugName", "%" + stdDrugInfo.getDrugName() + "%");
				countQuery.setString("drugName", "%" + stdDrugInfo.getDrugName() + "%");
			}
		}
		if (stdCompinfo != null) {
			if (StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				query.setString("compName", "%" + stdCompinfo.getCompName() + "%");
				countQuery.setString("compName", "%" + stdCompinfo.getCompName() + "%");
			}
		}
		
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());
		
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();
		
	}
	
	/**
	 * @category 根据id查询详情
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object> getBidPlanListByDrug(Pagination page, BidHospplan bidHospplan, HospInfo hospInfo, DicArea dicArea, DicHosptype dicHosptype) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select b.hosp_code, b.hosp_name, d.ar_fullname, b.hosp_classify, c.hosp_type, a.lasty_pro_amt,  ");
		sql.append(" a.plan_pro_amt, a.pro_method, a.quality_requires, a.proj_id, d.area_id,b.hosp_id");
		sql.append("  from bid_hospplan a  ");
		sql.append(" inner join std_hospinfo b on a.hosp_code = b.hosp_code ");
		sql.append(" left join dic_hosptype c on b.hosp_type_id = c.hosp_type_id  ");
		sql.append(" left join dic_area d on b.area_id = d.area_id ");
		sql.append(" where a.proj_id = :projId ");
		sql.append(" and a.drug_code = :drugId ");
		sql.append(" and b.`status`=1 ");
		
		if (hospInfo != null) {
			if (StringUtils.isNotEmpty(hospInfo.getHospCode())) {
				sql.append(" and b.hosp_code like :hospCode ");
			}
			if (StringUtils.isNotEmpty(hospInfo.getHospName())) {
				sql.append(" and b.hosp_name like :hospName ");
			}
			if (hospInfo.getHospClassify() != null && !"".equals(hospInfo.getHospClassify())) {
				sql.append(" and b.hosp_classify = :hospClassify");
			}
		}
		if (dicArea != null) {
			if (StringUtils.isNotEmpty(dicArea.getAreaid())) {
				sql.append(" and d.area_id like :areaId ");
			}
		}
		
		StringBuffer countSql = new StringBuffer();
		countSql.append(" select count(*) from ( ");
		countSql.append(sql.toString());
		countSql.append(" ) s where 1 = 1 ");
		Query query = session.createSQLQuery(sql.toString());
		Query countQuery = session.createSQLQuery(countSql.toString());
		query.setString("projId", bidHospplan.getProjId());
		countQuery.setString("projId", bidHospplan.getProjId());
		query.setString("drugId",bidHospplan.getDrugId());
		countQuery.setString("drugId", bidHospplan.getDrugId());
		
		if (hospInfo != null) {
			if (StringUtils.isNotEmpty(hospInfo.getHospCode())) {
				query.setString("hospCode", "%"+hospInfo.getHospCode().trim()+"%");
				countQuery.setString("hospCode", "%"+hospInfo.getHospCode().trim()+"%");
			}
			if (StringUtils.isNotEmpty(hospInfo.getHospName())) {
				query.setString("hospName", "%"+hospInfo.getHospName().trim()+"%");
				countQuery.setString("hospName", "%"+hospInfo.getHospName().trim()+"%");
			}
			if (hospInfo.getHospClassify() != null && !"".equals(hospInfo.getHospClassify())) {
				query.setInteger("hospClassify", hospInfo.getHospClassify());
				countQuery.setInteger("hospClassify", hospInfo.getHospClassify());
			}
		}
		if (dicArea != null) {
			if (StringUtils.isNotEmpty(dicArea.getAreaid())) {
				query.setString("areaId", "%"+dicArea.getAreaid()+"%");
				countQuery.setString("areaId", "%"+dicArea.getAreaid()+"%");
			}
		}
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());
		
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();
	}

	/**
	 * @category 获取医疗机构采购计划的表数据
	 * @author 韩守松
	 * @date   2015年6月16日
	 * @param  @param bidHospplan
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public List<BidHospplan> getBidHospplanList(BidHospplan bidHospplan) {
		try{
		StringBuffer hql = new StringBuffer();
		hql.append("from BidHospplan bh group by bh.compId ");
		Query query = this.getSession().createQuery(hql.toString());
		return query.list();
		}catch(Exception e){
			log.info("查询医疗机构信息异常-->", e);
		return new ArrayList<BidHospplan>();
		}
	}
	
	

	/**
	 * 
	 *@category  采购计划按医疗机构汇总
	 *@author 言科
	 *@date 2015年6月18日
	 *List<Object>
	 */
	@SuppressWarnings("unchecked")
	public List<Object> getBidCollectByHospList(Pagination page,BidHospplan bidHospplan, BidProject bidProject, HospInfo hospInfo,
			DicHosptype dicHosptype, DicArea dicArea) {
		Session session = sessionFactory.getCurrentSession();
		// 查询总记录数
		StringBuffer sql = new StringBuffer();

		sql.append(" select bhp.proj_id, bhp.hosp_code hosp_id, sh.hosp_code, sh.hosp_name, da.ar_fullname, sh.hosp_classify, dht.hosp_type,")
		   .append(" count(drug_code) as drug_count, bhp.drug_code drug_id from bid_hospplan bhp ")
		   .append(" inner join std_hospinfo sh on bhp.hosp_code = sh.hosp_code ")
		   .append(" left join dic_area da on sh.area_id = da.area_id ")
		   .append(" left join dic_hosptype dht on sh.hosp_type_id = dht.hosp_type_id ")
		   .append(" where proj_id =:projId");
		
		if (hospInfo != null) {
			if (StringUtils.isNotEmpty(hospInfo.getHospCode())) {
				sql.append(" and sh.hosp_code = :hospCode ");
			}
			if (StringUtils.isNotEmpty(hospInfo.getHospName())) {
				sql.append(" and (sh.hosp_name like :hospName OR sh.hosp_spel_code like :hospName)");
			}
			if (hospInfo.getHospClassify() != null && !"".equals(hospInfo.getHospClassify())) {
				sql.append(" and sh.hosp_classify = :hospClassify");
			}
		}
		if (dicArea != null) {
			if (StringUtils.isNotEmpty(dicArea.getAreaid())) {
				sql.append(" and da.area_id like :areaId ");
			}
		}
		sql.append(" group by bhp.hosp_code having count(bhp.drug_code) > 0  order by drug_count desc ");
		StringBuffer countSql = new StringBuffer();
		countSql.append(" select count(*) from ( ");
		countSql.append(sql.toString());
		countSql.append(" ) s where 1 = 1 ");
		Query query = session.createSQLQuery(sql.toString());
		Query countQuery = session.createSQLQuery(countSql.toString());
		query.setString("projId", bidProject.getProjId());
		countQuery.setString("projId", bidProject.getProjId());
		
		if (hospInfo != null) {
			if (StringUtils.isNotEmpty(hospInfo.getHospCode())) {
				query.setString("hospCode", hospInfo.getHospCode().trim());
				countQuery.setString("hospCode", hospInfo.getHospCode().trim());
			}
			if (StringUtils.isNotEmpty(hospInfo.getHospName())) {
				query.setString("hospName", "%"+hospInfo.getHospName().trim()+"%");
				countQuery.setString("hospName", "%"+hospInfo.getHospName().trim()+"%");
			}
			if (hospInfo.getHospClassify() != null && !"".equals(hospInfo.getHospClassify())) {
				query.setInteger("hospClassify", hospInfo.getHospClassify());
				countQuery.setInteger("hospClassify", hospInfo.getHospClassify());
			}
		}
		if (dicArea != null) {
			if (StringUtils.isNotEmpty(dicArea.getAreaid())) {
				query.setString("areaId", "%"+dicArea.getAreaid()+"%");
				countQuery.setString("areaId", "%"+dicArea.getAreaid()+"%");
			}
		}
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());
		
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();
	}
	
	/**
	 * 
	 *@category  采购计划按医疗机构汇总详情（根据id查询）
	 *@author 单大伟
	 *@date 2015年6月18日
	 *List<Object>
	 */
	@SuppressWarnings("unchecked")
	public List<Object> getBidCollectDetailsByHosp(Pagination page, BidHospplan bidHospplan, BidProject bidProject, StdDrugInfo stdDrugInfo, HospInfo hospInfo, DicDrugform dicDrugform, StdCompinfo stdCompinfo) {
		Session session = sessionFactory.getCurrentSession();
		// 查询总记录数
		StringBuffer sql = new StringBuffer();
		sql.append(" select b.drug_code, c.qualityspec_name drug_name, c.form_name, c.spec_name, b.factor, b.unit_name, b.material_name,  ");
		sql.append(" b.comp_name,a.lasty_pro_amt, a.plan_pro_amt, a.pro_method, a.quality_requires, a.proj_id,a.hosp_code,b.comp_code");
		sql.append(" from bid_hospplan a  ");
		sql.append("	LEFT JOIN  std_proddruginfo b ON a.drug_code = b.drug_code "); 
		sql.append("	LEFT JOIN   std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id");
		sql.append(" where a.proj_id = :projId ");
		sql.append(" and a.hosp_code = :hospId ");
		
		if (stdDrugInfo != null) {
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugCode())) {
				sql.append(" and b.drug_code like :drugCode ");
			}
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugName())) {
				sql.append(" and (c.qualityspec_name like :drugName OR c.qualityspec_name_spel like :drugName)");
			}
		}
		if (stdCompinfo != null) {
			if (StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				sql.append(" and b.comp_name like :compName");
			}
		}
		StringBuffer countSql = new StringBuffer();
		countSql.append(" select count(*) from ( ");
		countSql.append(sql.toString());
		countSql.append(" ) s where 1 = 1 ");
		Query query = session.createSQLQuery(sql.toString());
		Query countQuery = session.createSQLQuery(countSql.toString());
		query.setString("projId", bidHospplan.getProjId());
		countQuery.setString("projId", bidHospplan.getProjId());
		query.setString("hospId",bidHospplan.getHospId());
		countQuery.setString("hospId", bidHospplan.getHospId());
		
		if (stdDrugInfo != null) {
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugCode())) {
				query.setString("drugCode", stdDrugInfo.getDrugCode().trim());
				countQuery.setString("drugCode", stdDrugInfo.getDrugCode().trim());
			}
			if (StringUtils.isNotEmpty(stdDrugInfo.getDrugName())) {
				query.setString("drugName", "%"+stdDrugInfo.getDrugName()+"%");
				countQuery.setString("drugName", "%"+stdDrugInfo.getDrugName()+"%");
			}
		}
		if (stdCompinfo != null) {
			if (StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				query.setString("compName", "%"+stdCompinfo.getCompName()+"%");
				countQuery.setString("compName", "%"+stdCompinfo.getCompName()+"%");
			}
		}
		
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());
		
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();	
	}	
	
	/**
	 * 
	 *@category  采购计划按地区汇总
	 *@author wangbing
	 *@date 2015年6月18日
	 *List<Object>
	 */
	@Override
	public Pagination getBidCollectByAreaList(Pagination page){
		String projId = page.getConditions().get("projId");
		String areaId = page.getConditions().get("areaId");
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.area_id areaId,b.area_name areaName,b.ar_fullname arFullname, ")
		.append("	b.status status,COUNT(a.drug_code) drugCount ")
		.append("	FROM bid_hospplan a ")
		.append("	LEFT JOIN dic_area b ON b.area_id = a.area_id ") 
		.append("	WHERE a.proj_id = :projId ")
		.append("	AND a.area_id LIKE :areaId ")
		.append("	GROUP BY a.area_id order by a.area_id ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("areaId", areaId+"%");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category  采购计划按地区汇总详情（根据id查询）
	 *@author wangbing
	 *@date 2015年6月18日
	 *List<Object>
	 */
	@Override
	public Pagination getBidCollectDetailsByArea(Pagination page){
		String projId = page.getConditions().get("projId");
		String areaId = page.getConditions().get("areaId");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		String declareIns = page.getConditions().get("declareIns");
		StringBuffer sql = new StringBuffer()
		.append("	SELECT b.drug_code drugCode, c.qualityspec_name drugName, c.form_name formName, ")
		.append("	c.spec_name specName,b.factor factor, b.unit_name unit, b.material_name materialName,  ")
		.append("	b.comp_name compName,b.comp_code compCode,a.lasty_pro_amt lasyProAmt, a.plan_pro_amt planProAmt, ")
		.append("	a.pro_method proMethod,a.quality_requires qualityRequires, a.proj_id projId,a.drug_code drugId, ")
		.append("	CASE WHEN a.hea_bur_code IS NULL THEN d.hosp_name ELSE e.hea_bur_name END declareIns")
		.append("	FROM bid_hospplan a  ")
		.append("	LEFT JOIN  std_proddruginfo b ON a.drug_code = b.drug_code ") 
		.append("	LEFT JOIN   std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id")
		.append("	LEFT JOIN std_hospinfo d ON d.hosp_code = a.hosp_code")
		.append("	LEFT JOIN std_healthbureau e ON e.hea_bur_code = a.hea_bur_code")
		.append("	WHERE a.proj_id = :projId AND a.area_id = :areaId ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("areaId", areaId);
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (c.qualityspec_name like :drugName or c.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_name_spel like :compName )");
			params.put("compName", "%"+compName+"%");
		}
		if(StringUtils.isNotBlank(declareIns)){
			sql.append(" and (d.hosp_name like :declareIns or d.hosp_spel_code like :declareIns or e.hea_bur_name like :declareIns or e.bur_spel_code like :declareIns)");
			params.put("declareIns", "%"+declareIns+"%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 删除申报药品
	 *@author wangbing
	 *@date 2015年6月28日 
	 *@param page
	 *@return
	 */
	@Override
	public int deleteData(List<String> idList){
		StringBuffer sql = new StringBuffer()
		.append(" delete from bid_hospplan where proj_plan_id in (:idList)");
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("idList", idList);
		int count = query.executeUpdate();
		getSession().flush();
		return count;
	}
}